package Server.DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
 * 
 * @author kobi
 *
 */
public class UserQuery extends Query{
	
	public UserQuery() {
		super();
	}
	/**
	 * getUserByName
	 * @param userName
	 * @return
	 * @throws SQLException
	 */
	public User getUserByName(String userName) throws SQLException{
		setQuery("SELECT * FROM user " + "WHERE userName = '"+userName+"'");
		ResultSet rs = execQuery();
		if (rs.next()){
			User user = new User(rs.getInt(1), userName, rs.getString(3), rs.getInt(4), 
					rs.getInt(5),rs.getString(6), rs.getString(7));
			return user;
		}
		return null;
	}
	/**
	 * getUserByIdUser
	 * @param idUser
	 * @return
	 * @throws SQLException
	 */
	public User getUserByIdUser(int idUser) throws SQLException{
		setQuery("SELECT * FROM user " + "WHERE idUser = '"+idUser+"'");
		ResultSet rs = execQuery();
		if (rs.next()){
			User user = new User(idUser, rs.getString(2), rs.getString(3), rs.getInt(4), 
					rs.getInt(5),rs.getString(6), rs.getString(7));
			return user;
		}
		return null;
	}
	/**
	 * 
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<User> getAllUsers() throws SQLException{
		ArrayList<User> array = new ArrayList<User>();
		setQuery("SELECT * FROM user");
		ResultSet rs = execQuery();
		while (rs.next())
			array.add(getUserByIdUser(rs.getInt(1)));
		return array;
	}
	/**
	 * 
	 * @param idUser
	 * @throws SQLException
	 */
	public void setOnline(int idUser) throws SQLException{
		setQuery("UPDATE user "+"SET online = ? WHERE iduser = ? ");
		PreparedStatement update = getPS();
		update.setInt(1, 1);
		update.setInt(2, idUser);
		update.executeUpdate();
		update.close();
	}
	/**
	 * 
	 * @param idUser
	 * @throws SQLException
	 */
	public void setOffline(int idUser) throws SQLException{
		setQuery("UPDATE user "+"SET online = ? WHERE iduser = ? ");
		PreparedStatement update = getPS();
		update.setInt(1, 0);
		update.setInt(2, idUser);
		update.executeUpdate();
		update.close();
	}
	/**
	 * 
	 * @param userName
	 * @return
	 * @throws SQLException
	 */
	public boolean isExist(String userName) throws SQLException{
		setQuery("SELECT * FROM user " + "WHERE userName = ?");
		PreparedStatement query = getPS();
		query.setString(1, userName);
		if (query.executeQuery().next()){
			query.close();
			return true;
		}
		else {
			query.close();
			return false;
		}
	}
	/**
	 * 
	 * @param userName
	 * @param password
	 * @param permission
	 * @param firstName
	 * @param lastName
	 * @return
	 * @throws SQLException
	 */
	public int addUser(String userName, String password, int permission, String firstName, String lastName) throws SQLException{
		Info info = new Info();
		int newIdUser = info.getNewUserId();
		if (isExist(userName)) return 1;
		setQuery("INSERT INTO user(idUser, userName, password, permision, online, firstName, lastName) VALUES(?,?,?,?,?,?,?)");
		PreparedStatement query2 = getPS();
		query2.setInt(1, newIdUser);
		query2.setString(2, userName);
		query2.setString(3, password);
		query2.setInt(4, permission);
		query2.setInt(5, 0);
		query2.setString(6, firstName);
		query2.setString(7, lastName);
		query2.executeUpdate();
		query2.close();
		info.incUserId();
		info.close();
		return 0;
	}
	/**
	 * 
	 * @throws SQLException
	 */
	public void resetOnline() throws SQLException{
		ArrayList<User> array = getAllUsers();
		for (User user: array){
			setQuery("UPDATE user SET online='0' WHERE iduser=?");
			PreparedStatement query = getPS();
			query.setInt(1, user.getIdUser());
			query.executeUpdate();
			query.close();
		}
	}
}

